package com.ocom.accesservice.dao;


import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ocom.common.entity.human.EntityPersonnelInfo;
import com.ocom.common.vo.PersonVo;
import com.ocom.common.vo.access.NhCountVo;
import com.ocom.common.vo.access.NhVo;
import com.ocom.common.vo.access.SchoolVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;


@Mapper
public interface IDaoPersonnelInfo extends BaseMapper<EntityPersonnelInfo> {

    @Select({"<script>",
            "select \n" +
                    "i.ykm_str,i.encry_identity,i.p_name \n" +
                    "FROM personnel_info i \n" +
                    "left join personnel_ykm y on i.com_id=y.com_id and i.p_id=y.p_id\n" +
                    "where i.com_id=#{comId} and i.status=0 "+
                    " <when test=\"deptId !=null and  deptId !='-1'\" > "+
                    "  AND i.dept_id =#{deptId}  "+
                    " </when> "+
                    " order by y.update_time" +
                    " <when test=\"nums !=null and  nums !='-1'\" > "+
                    "  limit #{nums}  "+
                    " </when> "+
                    "</script>"})
    List<PersonVo> selectidentity(@Param("comId")Long comId, @Param("deptId")Long deptId, @Param("nums")Integer nums);


    @Select({"<script>",
            "select \n" +
                    "i.ykm_str,i.encry_identity,i.p_name,i.p_identity \n" +
                    "FROM personnel_info i \n" +
                    "left join personnel_ykm y on i.com_id=y.com_id and i.p_id=y.p_id\n" +
                    "where i.com_id=#{comId} and i.status=0 "+
                    " <when test=\"deptIds.size()>0 \" > "+
                        "and i.dept_id in" +
                        "<foreach collection='deptIds' item='item' open='(' separator=',' close=')'>" +
                        "#{item}" +
                        "</foreach>"+
                    " </when> "+
                    " order by y.update_time" +
                    " <when test=\"nums !=null and  nums !='-1'\" > "+
                    "  limit #{nums}  "+
                    " </when> "+
                    "</script>"})
    List<PersonVo> selectLimit(@Param("comId")Long comId, @Param("deptIds")List deptIds, @Param("nums")Integer nums);


    @Select({"<script>",
            "select \n" +
                    "COUNT(p_type = 104 OR NULL) as totalStudent,COUNT( (p_type = 104 and region_online=1) OR NULL) AS inSchoolStudent,COUNT(  (p_type = 104 and region_online=0) OR NULL) AS outSchoolStudent,  \n" +
                    "COUNT( (p_type = 102 OR p_type = 103) OR NULL ) as totalTeacher,COUNT( ((p_type = 102 OR p_type = 103) and region_online=1) OR NULL) AS inSchoolTeacher,COUNT(  ((p_type = 102 OR p_type = 103) and region_online=0) OR NULL) AS outSchoolTeacher  \n" +
                    "FROM personnel_info  " +
                    "where com_id=#{comId} and status=0 "+
                    "</script>"})
    NhVo selectNh(@Param("comId")Long comId);


    @Select({"<script>",
            "select \n" +
                    "COUNT(p_id) AS total," +
                    //總學生
                    "COUNT( (p_type=104 and region_orig_id=#{regionId}) or null) AS totalStudent," +
                    //本在校学生
                    "COUNT( (p_type=104 and (region_id=#{regionId} OR region_id=0)  and  region_orig_id = #{regionId}   and region_online=1 ) or null) AS inOurStudent," +
                    //本在外学生
                    "COUNT( (p_type=104  and region_orig_id=#{regionId} and  (region_id != #{regionId} AND region_id !=0 )  and region_online=1 ) or null) AS inOutsideStudent," +
                    //离校学生
                    "COUNT( (p_type=104 and region_orig_id=#{regionId} and region_online=0 ) or null) AS leaveSchoolStudent," +
                    //外校在本校学生
                    "COUNT( (p_type=104 and region_orig_id != #{regionId}  and region_id =#{regionId} and region_online=1  ) or null) AS outStudentInSchool," +
                    "COUNT( (p_type=102 OR p_type=103 )or null) AS teacher," +
                    "region_id\n" +
                    "FROM personnel_info  " +
                    "where com_id=#{comId} and status=0  "+
                    //本校教师职工
                    "and ((p_type in (102,103) and region_id=#{regionId} and region_online=1 ) " +
                    //本校区学生
                    "or (p_type=104  and region_orig_id =#{regionId} )" +
                    //其他校区
                    "or (p_type=104  and region_id=#{regionId} and region_online=1 )" +
                    ")" +
                    "</script>"})
    SchoolVo selectNhPart(@Param("comId")Long comId,@Param("regionId")Long regionId);



    @Select({"<script>",
            "select \n" +
                    "COUNT(g.id) " +
                    "FROM mj_group_per  g " +
                    "LEFT JOIN personnel_info p ON p.com_id=g.com_id AND p.p_id=g.p_id " +
                    "where g.com_id=#{comId} and p.status=0 and g.area_id = #{areaId} "+
                    "and p.p_photo_url  is not null  and  p.p_photo_url  !=''  "+
                    "</script>"})
    Integer selectFaceNumber(@Param("comId")Long comId,@Param("areaId")Long areaId);




    @Select({"<script>",
            "select \n" +
                    "COUNT( (p_type=102 OR p_type=103 ) or null ) AS t," +
                    "COUNT( ( (p_type=102 OR p_type=103 ) and region_id=#{regionA} and region_online=1  ) or null ) AS t1," +
                    "COUNT( ( (p_type=102 OR p_type=103 ) and region_id=#{regionB} and region_online=1  ) or null ) AS t2," +
                    "COUNT( ( (p_type=102 OR p_type=103 ) and region_id=#{regionC} and region_online=1  ) or null ) AS t3," +
                    "COUNT( ( (p_type=102 OR p_type=103 ) and region_online=0  ) or null ) AS t0," +


                    "COUNT( (p_type=104 AND region_orig_id=#{regionA} ) or null ) AS a," +
                    "COUNT( (p_type=104 AND region_orig_id=#{regionA} AND (region_id=#{regionA} OR region_id=0 )  AND region_online=1  ) or null ) AS a1," +
                    "COUNT( (p_type=104 AND region_orig_id=#{regionA} AND region_id=#{regionB}  AND region_online=1  ) or null ) AS a2," +
                    "COUNT( (p_type=104 AND region_orig_id=#{regionA} AND region_id=#{regionC}  AND region_online=1  ) or null ) AS a3," +
                    "COUNT( (p_type=104 AND region_orig_id=#{regionA} AND region_online=0  ) or null ) AS a0," +

                    "COUNT( (p_type=104 AND region_orig_id=#{regionB} ) or null ) AS b," +
                    "COUNT( (p_type=104 AND region_orig_id=#{regionB} AND region_id=#{regionA}  AND region_online=1  ) or null ) AS b1," +
                    "COUNT( (p_type=104 AND region_orig_id=#{regionB} AND (region_id=#{regionB} OR region_id=0 )  AND region_online=1  ) or null ) AS b2," +
                    "COUNT( (p_type=104 AND region_orig_id=#{regionB} AND region_id=#{regionC}  AND region_online=1  ) or null ) AS b3," +
                    "COUNT( (p_type=104 AND region_orig_id=#{regionB} AND region_online=0  ) or null ) AS b0," +


                    "COUNT( (p_type=104 AND region_orig_id=#{regionC} ) or null ) AS c," +
                    "COUNT( (p_type=104 AND region_orig_id=#{regionC} AND region_id=#{regionA}  AND region_online=1  ) or null ) AS c1," +
                    "COUNT( (p_type=104 AND region_orig_id=#{regionC} AND region_id=#{regionB}  AND region_online=1  ) or null ) AS c2," +
                    "COUNT( (p_type=104 AND region_orig_id=#{regionC} AND (region_id=#{regionC} OR region_id=0 )  AND region_online=1  ) or null ) AS c3," +
                    "COUNT( (p_type=104 AND region_orig_id=#{regionC} AND region_online=0  ) or null ) AS c0 " +

                    "FROM personnel_info  " +
                    "where com_id=#{comId} and status=0  "+
                    "</script>"})
    NhCountVo nhCount(@Param("comId")Long comId, @Param("regionA")Long regionA, @Param("regionB")Long regionB, @Param("regionC")Long regionC);
}
